﻿<html DIR="LTR" xmlns:tool="http://www.microsoft.com/tooltip" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ddue="http://ddue.schemas.microsoft.com/authoring/2003/5" xmlns:MSHelp="http://msdn.microsoft.com/mshelp">
  <head>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" />
    <META NAME="save" CONTENT="history" />
    <title>Understanding XA Transactions</title>
    
    <link rel="stylesheet" type="text/css" href="../local/Classic.css">
      
    </link>
    
    <script src="../local/script.js">
      
    </script><script src="../local/script_main.js">&amp;nbsp;</script>
  </head>
  <body>
    <!--Topic built:03/26/2010 02:49:39-->

    
    
    
    
    
    
    
    
    
    <div id="header">
      <table width="100%" id="topTable"><tr>
          <td align="left">
            <span id="headerBold">Understanding XA Transactions</span>
          </td>
          <td align="right">
            
          </td>
        </tr></table>
      
      
      
    </div>
    <div id="mainSection">
      
        
        
    <font color="DarkGray">
      
    </font>
    <p />
    
    <p />
  
        <div id="introductionSection" class="section">
    <p>The Microsoft SQL Server JDBC Driver provides support for Java Platform, Enterprise Edition/JDBC 2.0 optional distributed transactions. JDBC connections obtained from the <a href="95fc7b07-2498-4a7e-8f7f-ee0d86b598b4.htm">SQLServerXADataSource</a> class can participate in standard distributed transaction processing environments such as Java Platform, Enterprise Edition (Java EE) application servers. </p>
    <p>The classes for the distributed transaction implementation are as follows:</p>
    <table width="100%" cellspacing="0" cellpadding="0" border="1" style="background-color: #CCCCCC;"><tr>
          <th>
            Class
          </th>
          <th>
            Implements
          </th>
          <th>
            Description
          </th>
        </tr><tr>
        <td>
          <p>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</p>
        </td>
        <td>
          <p>javax.sql.XADataSource</p>
        </td>
        <td>
          <p>The class factory for distributed connections.</p>
        </td>
      </tr><tr>
        <td>
          <p>com.microsoft.sqlserver.jdbc.SQLServerXAResource</p>
        </td>
        <td>
          <p>javax.transaction.xa.XAResource</p>
        </td>
        <td>
          <p>The resource adaptor for the transaction manager.</p>
        </td>
      </tr></table>
    <div style="margin: .5em 1.5em .5em 1.5em"><b>Note: </b>
      XA distributed transaction connections default to the Read Committed isolation level.<p />
    </div>
  </div><h1 class="heading">Guidelines and Limitations when Using XA Transactions</h1><div id="sectionSection0" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following list provides information about specific limitations and guidance when using XA transactions:</p>
      <ul xmlns=""><li>
          On Windows XP:<br />
          When you use XA transactions with SQL Server through the Microsoft SQL Server JDBC driver, you may notice that XA transactions might not work. This issue only applies when the SQL Server that is participating in the XA transaction is running on Windows XP. On the other hand, the client applications running on Windows XP connecting to a remote SQL Server that is not running on Windows XP can participate in XA transactions. For more information about how to resolve this issue, see a hotfix provided at <a href="http://support.microsoft.com/kb/922668" target="_blank" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">Windows XP and XA Transactions</linkText></a>.<br />
        </li><li>
          On Windows Server 2003:<br />
          When you use XA transactions together with Microsoft Distributed Transaction Coordinator (MS DTC) on Windows Server 2003, you may notice that the <b>XAResource.setTransactionTimeout</b> method does not work. To resolve this issue, you must apply a hotfix provided at <a href="http://support.microsoft.com/kb/899756" target="_blank" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">MSDTC and XA Transactions</linkText></a> to each SQL Server machine that is participating in XA transactions. Without this fix the only valid timeout value is the default value of 0, meaning infinite timeout.<br />
        </li></ul>
      <p xmlns="">The following additional guidelines apply to tightly coupled transactions:</p>
      <ul xmlns=""><li>
          When you use XA transactions together with Microsoft Distributed Transaction Coordinator (MS DTC), you may notice that the current version of MS DTC does not support tightly coupled XA branch behavior. For example, MS DTC has a one-to-one mapping between an XA branch transaction ID (XID) and an MS DTC transaction ID and the work that is performed by loosely coupled XA branches is isolated from one another.<br />
          The hotfix provided at <a href="http://support.microsoft.com/kb/938653" target="_blank" alt=""><linkText xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">MSDTC and Tightly Coupled Transactions</linkText></a> enables the support for tightly coupled XA branches where multiple XA branches with same global transaction ID (GTRID) are mapped to a single MS DTC transaction ID. This support enables multiple tightly coupled XA branches to see one another's changes in the resource manager, such as SQL Server.<br />
        </li><li>
          A <a href="379857c3-9de1-4964-8782-32df317cbfbb.htm">SSTRANSTIGHTLYCPLD</a> flag allows the applications to use the tightly coupled XA transactions, which have different XA branch transaction IDs (XIDs) but have the same global transaction ID (GTRID). In order to use that feature, you must set the <a href="379857c3-9de1-4964-8782-32df317cbfbb.htm">SSTRANSTIGHTLYCPLD</a> on the <b>flags</b> parameter of the <b>XAResource.start</b> method:<br />
          <div class="sampleCode"><span codeLanguage="other"><pre>xaRes.start(xid, SQLServerXAResource.SSTRANSTIGHTLYCPLD);</pre></span></div>
        </li></ul>
    </content></div><h1 class="heading">Configuration Instructions</h1><div id="sectionSection1" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <p xmlns="">The following steps are required if you want to use XA data sources together with Microsoft Distributed Transaction Coordinator (MS DTC) for handling distributed transactions.</p>
      <div style="margin: .5em 1.5em .5em 1.5em" xmlns=""><b>Note: </b>
        The JDBC distributed transaction components are included in the xa directory of the JDBC driver installation. These components include the xa_install.sql and sqljdbc_xa.dll files.<p />
      </div>
    </content><sections xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <h1 class="heading" xmlns="">Running the MS DTC Service</h1><div id="sectionSection" class="section" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">The MS DTC service should be marked <b>Automatic</b> in Service Manager to make sure that it is running when the SQL Server service is started. To enable MS DTC for XA transactions, you must follow these steps:</p>
          <p xmlns="">On Windows XP and Windows Server 2003:</p>
          <ol xmlns=""><li>
              From <b>Control Panel</b>, open <b>Administrative Tools</b>, and then open <b>Component Services</b>. You can also click the <b>Start</b> button, click <b>Run</b>, type <b>dcomcnfg</b> in the <b>Open</b> box, and then press <b>OK</b> to open <b>Component Services</b>.<br />
            </li><li>
              Expand Component Services, Computers and right-click <b>My Computer</b>, and then select <b>Properties</b>.<br />
            </li><li>
              Click the <b>MSDTC</b> tab, and then click <b>Security Configuration</b>.<br />
            </li><li>
              Select the <b>Enable XA Transactions</b> check box, and then click <b>OK</b>. This will cause a MS DTC service restart.<br />
            </li><li>
              Click <b>OK</b> again to close the <b>Properties</b> dialog box, and then close <b>Component Services</b>.<br />
            </li><li>
              Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.<br />
            </li></ol>
          <p xmlns="">On Windows Vista:</p>
          <ol xmlns=""><li>
              Click the <b>Start</b> button, type <b>dcomcnfg</b> in the Start <b>Search</b> box, and then press ENTER to open <b>Component Services</b>. You can also type %windir%\system32\comexp.msc in the <b>Start</b> <b>Search</b> box to open <b>Component Services</b>.<br />
            </li><li>
              Expand Component Services, Computers, My Computer, and then Distributed Transaction Coordinator.<br />
            </li><li>
              Right-click <b>Local DTC</b> and then select <b>Properties</b>.<br />
            </li><li>
              Click the <b>Security</b> tab on the <b>Local DTC Properties</b> dialog box.<br />
            </li><li>
              Select the <b>Enable XA Transactions</b> check box, and then click <b>OK</b>. This will cause a MS DTC service restart.<br />
            </li><li>
              Click <b>OK</b> again to close the <b>Properties</b> dialog box, and then close <b>Component Services</b>.<br />
            </li><li>
              Stop and then restart SQL Server to make sure that it syncs up with the MS DTC changes.<br />
            </li></ol>
        </content></div>
      <h1 class="heading" xmlns="">Configuring the JDBC Distributed Transaction Components</h1><div id="sectionSection" class="section" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">You can configure the JDBC driver distributed transaction components by following these steps:</p>
          <ol xmlns=""><li>
              Copy the sqljdbc_xa.dll from the JDBC installation directory to the Binn directory of every SQL Server computer that will participate in distributed transactions.<br />
              <div style="margin: .5em 1.5em .5em 1.5em"><b>Note: </b>
                 If you are using XA transactions with a 32-bit SQL Server, use the sqljdbc_xa.dll file in the x86 folder, even if the SQL Server is installed on a x64 processor. If you are using XA transactions with a 64-bit SQL Server on the x64 processor, use the sqljdbc_xa.dll file in the x64 folder. If you are using XA transactions with a 64-bit SQL Server on an Itanium processor, use the sqljdbc_xa.dll file in the IA64 folder.<p />
              </div>
            </li><li>
              Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions. This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance.<br />
            </li><li>
              To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role.<br />
            </li></ol>
          <p xmlns="">You can configure only one version of the sqljdbc_xa.dll assembly on each SQL Server instance at a time. Applications may need to use different versions of the JDBC driver to connect to the same SQL Server instance by using the XA connection. In that case, sqljdbc_xa.dll, which comes with the newest JDBC driver, must be installed on the SQL Server instance.</p>
          <p xmlns="">There are three ways to verify the version of sqljdbc_xa.dll is currently installed on the SQL Server instance:</p>
          <ol xmlns=""><li>
              Open the LOG directory of SQL Server computer that will participate in distributed transactions. Select and open the SQL Server "ERRORLOG" file. Search for "Using 'SQLJDBC_XA.dll' version ..." phrase in the "ERRORLOG" file.<br />
            </li><li>
              Open the Binn directory of SQL Server computer that will participate in distributed transactions.Select sqljdbc_xa.dll assembly. <br />
              <ol><li>
                  On Windows Vista: Right-click sqljdbc_xa.dll and then select Properties. Then click the <b>Details</b> tab. The <b>File Version</b> field shows the version of sqljdbc_xa.dll that is currently installed on the SQL Server instance. <br />
                </li><li>
                  On Windows XP and Windows 2003 Server: Right-click sqljdbc_xa.dll and then select Properties. Next, click the <b>Version</b> tab. The <b>File Version</b> field shows the version of sqljdbc_xa.dll that is currently installed on the SQL Server instance.<br />
                </li></ol>
            </li><li>
              Set the logging functionality as shown in the code example in the next section. Search for "Server XA DLL version:..." phrase in the output log file.<br />
            </li></ol>
        </content></div>
      <h1 class="heading" xmlns="">Configuring the User-Defined Roles</h1><div id="sectionSection" class="section" xmlns=""><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
          <p xmlns="">To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role. For example, use the following Transact-SQL code to add a user named 'shelby' (SQL standard login user named 'shelby') to the SqlJDBCXAUser role:</p>
          <div class="sampleCode" xmlns=""><span codeLanguage="other"><pre>USE master
GO
EXEC sp_grantdbaccess 'shelby', 'shelby'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'shelby'</pre></span></div>
          <p xmlns="">SQL user-defined roles are defined per database. To create your own role for security purposes, you will have to define the role in each database, and add users in a per database manner. The SqlJDBCXAUser role is strictly defined in the master database because it is used to grant access to the SQL JDBC extended stored procedures that reside in master. You will have to first grant individual users access to master, and then grant them access to the SqlJDBCXAUser role while you are logged into the master database.</p>
        </content></div>
    </sections></div><h1 class="heading">Example</h1><div id="sectionSection2" class="section"><content xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5">
      <div class="sampleCode" xmlns=""><span codeLanguage="other"><pre>import java.net.Inet4Address;
import java.sql.*;
import java.util.Random;
import javax.transaction.xa.*;
import javax.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class testXA {

   public static void main(String[] args) throws Exception {

      // Create variables for the connection string.
      String prefix = "jdbc:sqlserver://";
      String serverName = "localhost";
      int portNumber = 1433;
      String databaseName = "AdventureWorks"; 
      String user = "UserName"; 
      String password = "*****";
      String connectionUrl = prefix + serverName + ":" + portNumber
         + ";databaseName=" + databaseName + ";user=" + user + ";password=" + password;

      try {
         // Establish the connection.
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         Connection con = DriverManager.getConnection(connectionUrl);

         // Create a test table.
         Statement stmt = con.createStatement();
         try {
            stmt.executeUpdate("DROP TABLE XAMin"); 
         }
         catch (Exception e) {
         }
         stmt.executeUpdate("CREATE TABLE XAMin (f1 int, f2 varchar(max))");
         stmt.close();
         con.close();

         // Create the XA data source and XA ready connection.
         SQLServerXADataSource ds = new SQLServerXADataSource();
         ds.setUser(user);
         ds.setPassword(password);
         ds.setServerName(serverName);
         ds.setPortNumber(portNumber);
         ds.setDatabaseName(databaseName);
         XAConnection xaCon = ds.getXAConnection();
         con = xaCon.getConnection();

         // Get a unique Xid object for testing.
         XAResource xaRes = null;
         Xid xid = null;
         xid = XidImpl.getUniqueXid(1);

         // Cleanup.
         con.close();
         xaCon.close();

         // Open a new connection and read back the record to verify that it worked.
         con = DriverManager.getConnection(connectionUrl);
         ResultSet rs = con.createStatement().executeQuery("SELECT * FROM XAMin");
         rs.next();
         System.out.println("Read -&gt; xid = " + rs.getString(2));
         rs.close();
         con.close();
      } 

      // Handle any errors that may have occurred.
      catch (Exception e) {
         e.printStackTrace();
      }
   }
}


class XidImpl implements Xid {

   public int formatId;
   public byte[] gtrid;
   public byte[] bqual;
   public byte[] getGlobalTransactionId() {return gtrid;}
   public byte[] getBranchQualifier() {return bqual;}
   public int getFormatId() {return formatId;}

   XidImpl(int formatId, byte[] gtrid, byte[] bqual) {
      this.formatId = formatId;
      this.gtrid = gtrid;
      this.bqual = bqual;
   }

   public String toString() {
      int hexVal;
      StringBuffer sb = new StringBuffer(512);
      sb.append("formatId=" + formatId);
      sb.append(" gtrid(" + gtrid.length + ")={0x");
      for (int i=0; i&lt;gtrid.length; i++) {
         hexVal = gtrid[i]&amp;0xFF;
         if ( hexVal &lt; 0x10 )
            sb.append("0" + Integer.toHexString(gtrid[i]&amp;0xFF));
         else
            sb.append(Integer.toHexString(gtrid[i]&amp;0xFF));
         }
         sb.append("} bqual(" + bqual.length + ")={0x");
         for (int i=0; i&lt;bqual.length; i++) {
            hexVal = bqual[i]&amp;0xFF;
            if ( hexVal &lt; 0x10 )
               sb.append("0" + Integer.toHexString(bqual[i]&amp;0xFF));
            else
               sb.append(Integer.toHexString(bqual[i]&amp;0xFF));
         }
         sb.append("}");
         return sb.toString();
      }

      // Returns a globally unique transaction id.
      static byte [] localIP = null;
      static int txnUniqueID = 0;
      static Xid getUniqueXid(int tid) {

      Random rnd = new Random(System.currentTimeMillis());
      txnUniqueID++;
      int txnUID = txnUniqueID;
      int tidID = tid;
      int randID = rnd.nextInt();
      byte[] gtrid = new byte[64];
      byte[] bqual = new byte[64];
      if ( null == localIP) {
         try {
            localIP = Inet4Address.getLocalHost().getAddress();
         }
         catch ( Exception ex ) {
            localIP =  new byte[] { 0x01,0x02,0x03,0x04 };
         }
      }
      System.arraycopy(localIP,0,gtrid,0,4);
      System.arraycopy(localIP,0,bqual,0,4);

      // Bytes 4 -&gt; 7 - unique transaction id.
      // Bytes 8 -&gt;11 - thread id.
      // Bytes 12-&gt;15 - random number generated by using seed from current time in milliseconds.
      for (int i=0; i&lt;=3; i++) {
         gtrid[i+4] = (byte)(txnUID%0x100);
         bqual[i+4] = (byte)(txnUID%0x100);
         txnUID &gt;&gt;= 8;
         gtrid[i+8] = (byte)(tidID%0x100);
         bqual[i+8] = (byte)(tidID%0x100);
         tidID &gt;&gt;= 8;
         gtrid[i+12] = (byte)(randID%0x100);
         bqual[i+12] = (byte)(randID%0x100);
         randID &gt;&gt;= 8;
      }
      return new XidImpl(0x1234, gtrid, bqual);
   }
}</pre></span></div>
    </content></div><span id="seeAlsoSpan"><h1 class="heading">See Also</h1></span><div id="seeAlsoSection" class="section" name="collapseableSection"><a href="afbb776f-05dc-4e79-bb25-2c340483e401.htm">Performing Transactions with the JDBC Driver</a><br /><br /></div><!--[if gte IE 5]>
			<tool:tip element="seeAlsoToolTip" avoidmouse="false"/><tool:tip element="languageFilterToolTip" avoidmouse="false"/><tool:tip element="roleInfoSpan" avoidmouse="false"/>
		<![endif]-->
      <div id="footer" class="section">
        
		<hr />
		
		<span id="fb" class="feedbackcss">
			
			
		</span>
		
		<a href="9bad553b-9e70-4696-8499-2e35f772a1e0.htm">
			
			© 2010 Microsoft Corporation. All rights reserved.
		</a>
 	
	
      </div>
    </div>
  </body>
</html>